package schoolsource.sql;

import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.logging.Level;

import schoolsource.config.ConfigurationItems;
import schoolsource.model.IntegerItem;
import schoolsource.model.Item;
import schoolsource.util.SchoolLogger;

/**
 *
 * @author Steven Steele
 * Copyright 2003
 *
 * A table representation that performs database updates and queries. Extensions
 * of this class are database-specific.
 */
public abstract class DBSQLAccessor {

	// Indicates the use of AND between criteria
	public static final byte AND = 1;
	// Indicates the use of OR between criteria
	public static final byte OR = 2;
	// The number of retries before trying to kill a connection
	private static final int patienceCount = 5;
	// The number of milliseconds to wait before trying again
	private static final int patienceTime = 3000;

	// The connection to the DB table
	private Connection tableConnection = null;
	// Used to indicate whether the DB connection is in use
	private boolean isOpen;
	//	The name of the DB table
	private String dataSource = null;
	//	The name of the table to be accessed
	private String tableName = null;

	/**
	 * Returns a new DBSQLAccessor for the data source and table specified. It
	 * sets up a connection to a specific table, however the connection is not
	 * opened in the constructor. The connection is opened in a separeate call to openConnection().
	 * @param newDataSource The name of the database as known by the server.
	 * @param newTableName The name of the table in the database.
	 */
	protected DBSQLAccessor(String newDataSource, String newTableName) {
		setDataSource(newDataSource);
		setTableName(newTableName);
	}

	/**
	 *
	 *  Takes in a string representing the name of a database. All further
	 *  calls to this DBSQLAccessor instance will be to this database unless
	 *  subsequently changed.
	 * @param newDataSource The name of the database
	 */
	public void setDataSource(String newDataSource) {
		if (newDataSource != null && !newDataSource.equals("")) {
			dataSource = newDataSource;
		} else {
			throw new InvalidParameterException("The data source must not be null or empty.\n");
		}
	}

	/**
	 * Takes in a string representing the name of a table within a database.
	 * All further calls to this DBSQLAccessor instance will be to this table unless
	 * subsequently changed.
	 * @param newTableName
	 */
	public void setTableName(String newTableName) {
		if (newTableName != null) {
			tableName = newTableName;
		} else {
			throw new InvalidParameterException("The table name must not be null or empty.\n");
		}
	}

	/**
	 * Opens a connection to the database and table specified either in the constructor
	 * or in subsequent calls to setDataSource and setTableName
	 */
	public final synchronized void openConnection() {
		boolean retrieved = false;
		int count = 0;
		while (!retrieved && count++ < patienceCount) {
			if (isOpen) {
				try {
					this.wait(patienceTime);
				} catch (InterruptedException ie) {
					throw new RuntimeException(
						"The thread was interrupted!\n" + ie.toString());
				}
			} else {
				try {
					///////////////////////////////////////////////////////////////////////////////////
					// This block of code may be extraneous and too specific to outside implementation
					try {
// made this change
//System.out.println("driver: " + ConfigurationItems.ORACLEDRIVER);
						Class.forName(ConfigurationItems.ORACLEDRIVER);
//						Class.forName("com.mysql.jdbc.Driver");
//						Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
					} catch (ClassNotFoundException e) {
						SchoolLogger.logger.log(
							Level.SEVERE,
							"Could not find the Oracle driver");
					}
					///////////////////////////////////////////////////////////////////////////////////

// made this change
//System.out.println("dataSource: " + dataSource);
					if (ConfigurationItems.USEDBPASSWORD) {
						tableConnection = DriverManager.getConnection(dataSource, "mystud2_schoolUs", "sUpErSeCrEtPaSsWoRd");
					}
					else {
						tableConnection = DriverManager.getConnection(dataSource);
					}
					isOpen = true;
					retrieved = true;
				} catch (SQLException sqle) {
					retrieved = false;
					SchoolLogger.logger.log(
						Level.SEVERE,
						"Could not open the database.\n" + sqle.toString());
				}
			}
		}

		if (!retrieved) {
			// Should the connection be closed and re-tried returning?
			// closeConnection();
			// try {
			//   tableConnection = DriverManager.getConnection(dataSource);
			// }
			// catch (SQLException sqle) {
			//   throw new RuntimeException("Couldn't write to the database.\n" + sqle.toString());
			// }
		}

		return;
	}

	/**
	 * Closes any connection to a database. Whether or not a connection exists
	 * when this method is called, the expected outcome is that no connection is open when
	 * this method finishes, whether or not the connection is null.
	 */
	public final synchronized void closeConnection() {

		try {
			tableConnection.close();
		} catch (SQLException sqle) {
			//throw new RuntimeException("Couldn't close the database.\n" + sqle.toString());
			SchoolLogger.logger.log(
				Level.WARNING,
				"Couldn't close the database.",
				sqle);
		} catch (NullPointerException npe) {
			SchoolLogger.logger.log(
				Level.WARNING,
				"Couldn't close the database because no reference to the connection exists.",
				npe);
		} finally {
			isOpen = false;
		}
	}

	/**
	 * Takes in an array of Items whose values will be used as a key to delete
	 * rows from a table.
	 * @param keyList The key items to indicate which rows are to be deleted.
	 * @param combination Indicates whether the key items are combined by AND or OR.
	 */
	public void delete(Item keyList[], byte combination) {
		StringBuffer deleteString =
			new StringBuffer("DELETE FROM " + tableName + " WHERE ");
		for (int i = 0; i < keyList.length;) {
			deleteString.append(
				keyList[i].getFieldName()
					+ " = "
					+ keyList[i].getDBFormattedString(this));
			if (++i < keyList.length) {
				switch (combination) {
					case AND :
						deleteString.append(" AND ");
						break;
					case OR :
						deleteString.append(" OR ");
						break;
					default :
						SchoolLogger.logger.log(
							Level.INFO,
							""
								+ combination
								+ " is not a valid type of concatenator.");
				}
			}
		}
		deleteString.append(";");
		performDatabaseUpdate(deleteString.toString());
	}

	/**
	 * Takes in an array of Items whose values will be saved to the database, an array
	 * of Items whose field values will be used as keys, and a byte representing either
	 * OR or AND (used on key query).
	 * @param itemList The items to be saved.
	 * @param keyList The key to be used to update an existing row.
	 * @param combination Indicates whether the key items are combined by AND or OR.
	 */
	public void save(Item itemList[], Item keyList[], byte combination) {
		StringBuffer updateString =
			new StringBuffer("UPDATE " + tableName + " SET ");
		StringBuffer whereString = new StringBuffer(" WHERE ");
		for (int i = 0; i < keyList.length;) {
			whereString.append(
				keyList[i].getFieldName()
					+ " = "
					+ keyList[i].getDBFormattedString(this));
			if (++i < keyList.length) {
				switch (combination) {
					case AND :
						whereString.append(" AND ");
						break;
					case OR :
						whereString.append(" OR ");
						break;
					default :
						SchoolLogger.logger.log(
							Level.INFO,
							""
								+ combination
								+ " is not a valid type of concatenator.");
				}
			}
		}

		if (itemList.length > 0) {
			ArrayList al =
				performDatabaseQuery(
					"SELECT * FROM " + tableName + whereString,
					keyList);
			if (al.size() > 0) {
				for (int i = 0; i < itemList.length;) {
					updateString.append(
						itemList[i].getFieldName()
							+ " = "
							+ itemList[i].getDBFormattedString(this));
					if (++i < itemList.length) {
						updateString.append(", ");
					}
				}
				updateString.append(whereString.toString() + ";");
				performDatabaseUpdate(updateString.toString());
			} else {

				StringBuffer insertString =
					new StringBuffer("INSERT INTO " + tableName + " (");
				StringBuffer fields = new StringBuffer();
				StringBuffer values = new StringBuffer();
				for (int i = 0; i < itemList.length;) {
					fields.append(itemList[i].getFieldName());
					values.append(itemList[i].getDBFormattedString(this));
					if (++i < itemList.length) {
						// if this is not the last item
						fields.append(", ");
						values.append(", ");
					}
				}
				insertString.append(
					fields.toString()
						+ ") VALUES ("
						+ values.toString()
						+ ");");
				performDatabaseUpdate(insertString.toString());
			}
		}
	}

	/**
	 * Takes in an array of Items whose values will be set by the values in the database, an array
	 * of Items whose field values will be used as keys, and a byte representing either
	 * OR or AND (used on key query). load assumes only one set of data will match the key.
	 * If more than one set of data match the key, then the first match will be used.
	 * Use get2DArray to retrieve multiple matches.
	 * @param itemList The list of items to be populated by values in the matching row.
	 * @param keyList The key(s) to match.
	 * @param combination Indicates whether the key items are combined by AND or OR.
	 */
	public void load(Item itemList[], Item keyList[], byte combination) {

		StringBuffer queryString =
			new StringBuffer("SELECT * FROM " + tableName + " ");
		StringBuffer whereString = new StringBuffer("WHERE ");

		// Generate 'WHERE' criteria
		for (int i = 0; i < keyList.length;) {
			whereString.append(
				keyList[i].getFieldName()
					+ " = "
					+ keyList[i].getDBFormattedString(this));
			if (++i < keyList.length) {
				switch (combination) {
					case AND :
						whereString.append(" AND ");
						break;
					case OR :
						whereString.append(" OR ");
						break;
					default :
						SchoolLogger.logger.log(
							Level.INFO,
							""
								+ combination
								+ " is not a valid type of contatenator.");
				}
			}
		}

		// Create full query string
		queryString.append(whereString.toString() + ";");

		ArrayList al = performDatabaseQuery(queryString.toString(), itemList);

		// Put values in the objects that were passed in
		// This only uses the first match, so if there is more than one match, it only uses one
		int index;
		Hashtable results[] =
			(Hashtable[]) al.toArray(new Hashtable[al.size()]);
		for (int i = 0; i < itemList.length && results.length != 0; i++) {
			itemList[i].createFromDBString(
				(String) results[0].get(itemList[i].getFieldName()),
				this);
		}
	}

	/**
	 * Takes in an array of Items whose values will be retrieved from the database, an array
	 * of Items whose field values will be used as keys, and a byte representing either
	 * OR or AND (used on key query). An array of hashtables is returned, each hashtable contains
	 * one set of results. Use the column name as a key to retrieve the values of each column.
	 * This has not been tested with large tables and using this with large tables is not
	 * recommended.
	 * @param itemList The list of columns to load.
	 * @param keyList The key(s) to match.
	 * @param combination Indicates whether the key items are combined by AND or OR.
	 * @return An array of hashtables with several matches to a query
	 */
	public Hashtable[] get2DArray(Item itemList[], Item keyList[], byte combination) {
		StringBuffer queryString =
			new StringBuffer("SELECT * FROM " + tableName + " ");
		StringBuffer whereString = new StringBuffer("WHERE ");
		for (int i = 0; i < keyList.length;) {
			whereString.append(
				keyList[i].getFieldName()
					+ " = "
					+ keyList[i].getDBFormattedString(this));
			if (++i < keyList.length) {
				switch (combination) {
					case AND :
						whereString.append(" AND ");
						break;
					case OR :
						whereString.append(" OR ");
						break;
					default :
						SchoolLogger.logger.log(
							Level.INFO,
							""
								+ combination
								+ " is not a valid type of contatenator.");
				}
			}
		}
		if (keyList.length == 0) {
			whereString = new StringBuffer("");
		}

		queryString.append(whereString.toString() + ";");

		return (Hashtable[]) performDatabaseQuery(
			queryString.toString(),
			itemList).toArray(
			new Hashtable[5]);
	}

	/**
	 * Takes in an array of items to match, an array of items to use as the key, and
	 * a byte indicating whether the key is combined using AND or OR.
	 * @param itemList List of items to populate. In the case of multiple matches, the last match
	 * populates the array of Items.
	 * @param keyList List of keys to match.
	 * @param combination combination Indicates whether the key items are combined by AND or OR.
	 * @return Indicates whether one or more rows exists in the table that match the keys.
	 */
	public boolean exists(Item itemList[], Item keyList[], byte combination) {
		StringBuffer queryString =
			new StringBuffer("SELECT * FROM " + tableName + " ");
		StringBuffer whereString = new StringBuffer("WHERE ");

		for (int i = 0; i < keyList.length;) {
			whereString.append(
				keyList[i].getFieldName()
					+ " = "
					+ keyList[i].getDBFormattedString(this));
			if (++i < keyList.length) {
				switch (combination) {
					case AND :
						whereString.append(" AND ");
						break;
					case OR :
						whereString.append(" OR ");
						break;
					default :
						SchoolLogger.logger.log(
							Level.INFO,
							""
								+ combination
								+ " is not a valid type of contatenator.");
				}
			}
		}
		boolean returnBool = false;
		if (performDatabaseQuery(queryString.toString() + whereString.toString(), itemList).size()
			!= 0) {
			returnBool = true;
		}

		return returnBool;
	}

	/**
	 * Performs a database update using the provided query.
	 * @param query A database query.
	 * @return an integer indicating the number of rows updated by the query.
	 */
	public synchronized int performDatabaseUpdate(String query) {
		int results = 0;
		try {
			openConnection();
			Statement statement = tableConnection.createStatement();

			SchoolLogger.logger.log(Level.INFO, " - query: " + query);

			results = statement.executeUpdate(query);
			SchoolLogger.logger.log(Level.INFO, " - results: " + results);
		} catch (SQLException sqle) {
			SchoolLogger.logger.log(
				Level.SEVERE,
				"An SQL error occurred!",
				sqle);
			//      sqle.printStackTrace(Log.out);
			throw new RuntimeException(
				"Couldn't write to the database.\n" + sqle.toString());
		} catch (NullPointerException npe) {
			throw new RuntimeException(
				"The table connection is not open.\n" + npe.toString());
		} finally {
			closeConnection();
		}

		return results;
	}

	/**
	 * Queries the database using the passed-in SQL statement and
	 * @param query The SQL statment to execute.
	 * @param itemList List of items to populate. In the case of multiple matches, the last match
	 * populates the array of Items.
	 * @return A list of hashtables containing all of the sets of results - one hashtable per match.
	 */
	public synchronized ArrayList performDatabaseQuery(String query, Item itemList[]) {
		boolean returnBoolean = false;
		ResultSet results = null;
		ArrayList al = null;
		try {
			openConnection();
			
			System.out.println("connection autocommit?" + tableConnection.getAutoCommit());
			
			Statement statement = tableConnection.createStatement();
			SchoolLogger.logger.log(Level.INFO, " - query: " + query);
			results = statement.executeQuery(query);
			al = parseResultSet(results, itemList);
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw new RuntimeException(
				"Couldn't read from the database.\n" + sqle.toString());
		} finally {
			closeConnection();
		}

		return al;
	}

	/**
	 * A utility function that parses a result set into an array list containing all of the matches,
	 * and populates the array of Items that was passed in with the last matching results.
	 * @param rs The result of a database query.
	 * @param itemList The list of items to populate.
	 * @return A list of hash tables containing all matches contained in the result set.
	 */
	private ArrayList parseResultSet(ResultSet rs, Item itemList[]) {
		ArrayList ra = new ArrayList();
		Hashtable ht = null;
		String readValue;

		try {
			while (rs.next()) {
				ht = new Hashtable(itemList.length);
				for (int i = 0; i < itemList.length; i++) {
					//SchoolLogger.logger.log(Level.INFO, "itemList["+i+"].getFieldName(): " + [i].getFieldName());
					readValue = rs.getString(itemList[i].getFieldName());
					if (readValue != null) {
						ht.put(itemList[i].getFieldName(), readValue);
					}
				}
				ra.add(ht);
			}
		} catch (SQLException sqle) {
			//      sqle.printStackTrace();
			SchoolLogger.logger.log(
				Level.SEVERE,
				"DBSQLAccessor.parseResultSet(): An exception occurred.",
				sqle);
			//      Log.out.println(" " + sqle.toString());
			//      sqle.printStackTrace(Log.out);
		}
		//    SchoolLogger.logger.log(Level.INFO, "############ size "+ra.size());
		return ra;
	}

	/**
	 * Takes in an Item, puts the last id into the item, and returns the integer
	 * that it retrieved.
	 * @param keylist An item for the column where the last value is to be selected. This item
	 * is populated with the value that is returned.
	 * @return The last (highest) key in a column.
	 */
	public synchronized int getLastID(IntegerItem keylist) {
		ResultSet rs;
		String query =
			"SELECT MAX("
				+ keylist.getFieldName()
				+ ") FROM "
				+ tableName
				+ ";";
		try {
			openConnection();
			SchoolLogger.logger.log(Level.INFO, " - query: " + query);
			Statement statement = tableConnection.createStatement();
			rs = statement.executeQuery(query);
			rs.next();
			keylist.setTrueValue(new Integer(rs.getInt(1)));
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw new RuntimeException(
				"Couldn't write to the database.\n" + sqle.toString());
		} finally {
			closeConnection();
		}

		return ((Integer) keylist.getObject()).intValue();
	}

	/**
	 * Takes in an Item, puts the new id into the item, and returns the integer
	 * representing the next available index for the Request object. This is simply
	 * the next index after the highest existing index. It does not find gaps.
	 * @param keylist An Item for the column where a new key is needed. This item
	 * is populated withthe value that is returned.
	 * @return The integer the comes after the largest integer in the column.
	 */
	public synchronized int getAvailableID(IntegerItem keylist) {
		int returnVal = getLastID(keylist);

		keylist.setTrueValue(new Integer(returnVal + 1));

		return returnVal + 1;
	}

	/**
	 * Takes in a string representing an integer and
	 * returns the string used by the particular database to specify
	 * integers.
	 * @param trueString A Java string representation of an integer.
	 * @return A string formatted in a particular database's syntax for an integer.
	 */
	public abstract String getDBFormattedInteger(String trueString);

	/**
	 * Takes in a database's string representation of
	 * an integer and returns just the integer in string form.
	 * @param dbString A string formatted in a particular database's syntax for an integer.
	 * @return A Java string representation of an integer.
	 */
	public abstract String removeDBTagsFromInteger(String dbString);

	/**
	 * Takes in a string representing a double and
	 * returns the string used by the particular database to specify
	 * doubles.
	 * @param trueString A Java string representation of a double.
	 * @return A string formatted in a particular database's syntax for a double.
	 */
	public abstract String getDBFormattedDouble(String trueString);

	/**
	 * Takes in a database's string representation of
	 * a double and returns just the double in string form.
	 * @param dbString A string formatted in a particular database's syntax for a double.
	 * @return A Java string representation of a double.
	 */
	public abstract String removeDBTagsFromDouble(String dbString);

	/**
	 * Takes in a string representing a date and
	 * returns the string used by the particular database to specify
	 * dates.
	 * @param trueString A Java string representation of a date.
	 * @return A string formatted in a particular database's syntax for a date.
	 */
	public abstract String getDBFormattedDate(String trueString);

	/**
	 * Takes in a database's string representation of
	 * a date and returns just the date in string form.
	 * @param dbString A string formatted in a particular database's syntax for a date.
	 * @return A Java string representation of a date.
	 */
	public abstract String removeDBTagsFromDate(String dbString);

	/**
	 * Takes in a string representing a string and
	 * returns the string used by the particular database to specify
	 * strings.
	 * @param trueString A Java string.
	 * @return A string formatted in a particular database's syntax for a string.
	 */
	public abstract String getDBFormattedString(String trueString);

	/**
	 * Takes in a database's string representation of
	 * a string and returns just the string in string form.
	 * @param dbString A string formatted in a particular database's syntax for a string.
	 * @return A Java string.
	 */
	public abstract String removeDBTagsFromString(String dbString);

	/**
	 * Takes in a string representing a password and
	 * returns the string used encrypt passwords.
	 * @param trueString A Java string representation of a password.
	 * @return A string formatted in a particular database's syntax for a password.
	 */
	public abstract String getDBFormattedPassword(String trueString);

	/**
	 * Takes in a database's string representation of
	 * a password and returns just the password in string form. This should
	 * never be used. It will not return the unencrypted password.
	 * @param dbString A string formatted in a particular database's syntax for a password.
	 * @return A Java string representation of a password.
	 */
	public abstract String removeDBTagsFromPassword(String dbString);

	/**
	 * Takes in a string representing a boolean and
	 * returns the string used by the particular database to specify
	 * booleans.
	 * @param trueString A Java string representation of a boolean.
	 * @return A string formatted in a particular database's syntax for a boolean.
	 */
	public abstract String getDBFormattedBoolean(String trueString);

	/**
	 * Takes in a database's string representation of
	 * a boolean and returns just the boolean in string form.
	 * @param dbString A string formatted in a particular database's syntax for a boolean.
	 * @return A Java string representation of a boolean.
	 */
	public abstract String removeDBTagsFromBoolean(String dbString);
}
